package com.bdc.Util;

import java.sql.*;

/**
 * JDBC-Util
 */
public class JDBCUtil {

    private static final String DBDRIVER = "com.mysql.jdbc.Driver";// 驱动类类名
    private static final String DBURL = "jdbc:mysql://localhost:3306/users?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai";// 连接URL
    private static final String DBUSER = "root";// 数据库用户名
    private static final String DBPASSWORD = "123456";// 数据库密码
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    /*
     * 获取数据库连接
     */
    public static Connection getConnection() {
        try {
            Class.forName(DBDRIVER);// 注册驱动
            conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);// 获得连接对象
            System.out.println("成功加载SQL Server驱动程序");
        } catch (ClassNotFoundException e) {// 捕获驱动类无法找到异常

            System.out.println("找不到SQL Server驱动程序");
            e.printStackTrace();

        } catch (SQLException e) {// 捕获SQL异常

            e.printStackTrace();
        }
        return conn;
    }

    public static ResultSet select(String sql) throws Exception {
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            rs = ps.executeQuery();
            return rs;
        } catch (SQLException sqle) {
            throw new SQLException("select data Exception: "
                    + sqle.getMessage());
        } catch (Exception e) {
            throw new Exception("System error: " + e.getMessage());
        }
    }

    /*
     * 增删改均调用这个方法
     */
    public static void update(String sql) throws Exception {
        try {
            conn = getConnection();
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException sqle) {
            throw new SQLException("insert data Exception: "
                    + sqle.getMessage());
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception e) {
                throw new Exception("ps close exception: " + e.getMessage());
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                throw new Exception("conn close exception: " + e.getMessage());
            }
        }
    }

    public static void closeAll() {
        try {
            rs.close();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
